﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
//using System.Data.Sql;

namespace SeniorProjectWS
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        //static String connection = @"Data Source=MTTH_\SQLSERVER2008;Initial Catalog=GPSLocation;Persist Security Info=True;User ID=sa;Password=123456";
        //static String connection = @"workstation id=GPSLocation.mssql.somee.com;packet size=4096;user id=MTTH;pwd=16011990;data source=GPSLocation.mssql.somee.com;persist security info=False;initial catalog=GPSLocation";
        //static String connection = @"server=localhost;User Id=root;database=senior_project;Persist Security Info=True";
        static String connection = @"server=192.168.1.250;User Id=seniorproject;password=seniorproject;database=seniorproject";
        String latitude = "0.000001";
        String longitude = "0.000001";
        
        [WebMethod]
        public string HelloWorld()
        {
            return "Mai Tran Hong Phuong";
        }
        [WebMethod]
        public DataSet getDataset(String name)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select * from accounts where username='" + name + "'";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();
            return ds;
        }

        public void AddRecord(String name, String latitude, String longitude)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connection);
                conn.Open();
                String sql = "select * from accounts";
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "accounts");
                conn.Close();

                DataTable dt = ds.Tables["accounts"];
                DataRow dr = dt.NewRow();

                dr["username"] = name;
                dr["latitude"] = latitude;
                dr["longitude"] = longitude;

                dt.Rows.Add(dr);
                //ds.Tables.Add(dt);
                if (ds != null)
                {
                    conn.Open();
                    adapter.UpdateCommand = new MySqlCommandBuilder(adapter).GetUpdateCommand();
                    int NumRows = adapter.Update(ds, "accounts");
                    if (NumRows > 0) adapter.Fill(ds);
                    conn.Close();
                }
            }
            catch (Exception e)
            {
            }
        }

        [WebMethod]
        public String UpdateRecord(String name, String latitude, String longitude)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(connection);
                conn.Open();
                String sql = "select * from accounts where username='" + name + "'";
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "accounts");
                conn.Close();

                DataTable dt = ds.Tables["accounts"];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["username"].ToString().Equals(name))
                        {
                            dr["latitude"] = latitude;
                            dr["longitude"] = longitude;
                        }
                    }

                    if (ds != null)
                    {
                        conn.Open();
                        adapter.UpdateCommand = new MySqlCommandBuilder(adapter).GetUpdateCommand();
                        int NumRows = adapter.Update(ds, "accounts");
                        if (NumRows > 0) adapter.Fill(ds);
                        conn.Close();
                    }
                    return latitude + "-" + longitude;
                }
                else return "Ko co ten nay";
            }
            catch (Exception e)
            {
                return "NotOK";
            }
        }

        [WebMethod]
        public String getPosition(String name)
        {
            DataSet ds = getDataset(name);
            DataTable dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                latitude = dr["latitude"].ToString();
                longitude = dr["longitude"].ToString();
            }

            return latitude + "-" + longitude;
        }

        [WebMethod]
        public String Login(String username, String password)
        {
            DataSet ds = getDatasetLogin(username, password);
            DataTable dt = ds.Tables[0];

            return dt.Rows.Count.ToString();
        }
        public DataSet getDatasetLogin(String name, String pass)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select * from accounts where username='" + name + "' and password='" + pass + "'";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();
            return ds;
        }
        /*
        [WebMethod]
        public String FindFriend(String username)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select username from friends,accounts where user1_id = accounts.id and user2_id in (select id from accounts where username='" + username + "') union select username from friends,accounts where user2_id = accounts.id and user1_id in (select id from accounts where username='" + username + "')";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();

            DataTable dt = ds.Tables[0];
            String friendlist = "";

            foreach (DataRow dr in dt.Rows)
            {
                friendlist += dr["username"].ToString() + "-";
            }
            if (friendlist.Length > 0) { if (friendlist[friendlist.Length - 1].ToString().Equals("-")) friendlist = friendlist.Substring(0, friendlist.Length - 1); }
            else friendlist = "Nobody";
            return friendlist;
        }*/

        [WebMethod]
        public String isFriends(String username1, String username2)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select COUNT(id) as total from friends where (user1_id in (select id from accounts where username='" + username1 + "') and user2_id in (select id from accounts where username='" + username2 + "')) or (user1_id in (select id from accounts where username='" + username2 + "') and user2_id in (select id from accounts where username='" + username1 + "'))";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();

            DataTable dt = ds.Tables[0];
            String ketqua = "NotOK";

            if (dt.Rows[0]["total"].ToString().Equals("1")) ketqua = "OK";

            return ketqua;
        }

        [WebMethod]
        public String isUsername(String username)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select COUNT(id) as total from accounts where username='" + username + "'";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();

            DataTable dt = ds.Tables[0];
            String ketqua = "NotOK";

            if (dt.Rows[0]["total"].ToString().Equals("1")) ketqua = "OK";

            return ketqua;
        }

        [WebMethod]
        public String isSentFriendRequest(String username1, String username2)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select COUNT(id) as total from friend_requests where sender_id in (select id from accounts where username='" + username1 + "') and receiver_id in (select id from accounts where username='" + username2 + "')";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();

            DataTable dt = ds.Tables[0];
            String ketqua = "NotOK";

            if (dt.Rows[0]["total"].ToString().Equals("1")) ketqua = "OK";

            return ketqua;
        }

        [WebMethod]
        public String SendFriendRequest(String username1, String username2)
        {
            if (isUsername(username1).Equals("OK") && isUsername(username2).Equals("OK"))
            {
                if (isFriends(username1, username2).Equals("OK")) return "You and this person are friends!";
                else
                {
                    if (isSentFriendRequest(username1, username2).Equals("OK")) return "The request was sent ago!";
                    else
                    {
                        MySqlConnection conn = new MySqlConnection(connection);
                        conn.Open();
                        String sql = "insert into friend_requests(sender_id,receiver_id) values ((select id from accounts where username='" + username1 + "'),(select id from accounts where username='" + username2 + "'))";
                        MySqlCommand sc = new MySqlCommand(sql, conn);
                        sc.ExecuteNonQuery();
                        conn.Close();
                        return "Your request was sent successfully!";
                    }
                }
            }
            else return "This person is not exist!";
        }

        [WebMethod]
        public String getFriendRequest(String username)
        {
            if (isUsername(username).Equals("OK"))
            {
                MySqlConnection conn = new MySqlConnection(connection);
                conn.Open();
                String sql = "select username from accounts where id in (select sender_id from accounts,friend_requests where accounts.id=friend_requests.receiver_id and username='" + username + "')";
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();

                DataTable dt = ds.Tables[0];
                String requestlist = "";

                foreach (DataRow dr in dt.Rows)
                {
                    requestlist += dr["username"].ToString() + "-";
                }
                if (requestlist.Length > 0) { if (requestlist[requestlist.Length - 1].ToString().Equals("-")) requestlist = requestlist.Substring(0, requestlist.Length - 1); }
                else requestlist = "Nobody";
                return requestlist;
            }
            else return "This username is not exist!";
        }

        [WebMethod]
        public String AcceptFriendRequest(String username1, String username2)
        {
            if (isFriends(username1, username2).Equals("OK"))
            {
                MySqlConnection conn = new MySqlConnection(connection);
                String sql = "delete from friend_requests where sender_id in (select id from accounts where username='" + username1 + "') and receiver_id in (select id from accounts where username='" + username2 + "')";
                MySqlCommand sc = new MySqlCommand(sql, conn);
                sc.ExecuteNonQuery();
                conn.Close();
                return "You and " + username2 + " are friends!";
            }
            else
            {
                MySqlConnection conn = new MySqlConnection(connection);
                conn.Open();
                String sql = "insert into friends(user1_id,user2_id) values ((select id from accounts where username='" + username1 + "'),(select id from accounts where username='" + username2 + "'))";
                MySqlCommand sc = new MySqlCommand(sql, conn);
                sc.ExecuteNonQuery();

                sql = "delete from friend_requests where sender_id in (select id from accounts where username='" + username1 + "') and receiver_id in (select id from accounts where username='" + username2 + "')";
                sc = new MySqlCommand(sql, conn);
                sc.ExecuteNonQuery();
                conn.Close();
                return "You and " + username1 + " now are friends!";
            }
        }

        [WebMethod]
        public String DeclineFriendRequest(String username1, String username2)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "delete from friend_requests where sender_id in (select id from accounts where username='" + username1 + "') and receiver_id in (select id from accounts where username='" + username2 + "')";
            MySqlCommand sc = new MySqlCommand(sql, conn);
            sc.ExecuteNonQuery();
            conn.Close();
            return "The request was declined!";
        }

        [WebMethod]
        public String UnFriend(String username1, String username2)
        {
            if (isFriends(username1, username2).Equals("OK"))
            {
                MySqlConnection conn = new MySqlConnection(connection);
                conn.Open();
                String sql = "delete from friends where (user1_id in (select id from accounts where username='" + username1 + "') and user2_id in (select id from accounts where username='" + username2 + "')) or (user1_id in (select id from accounts where username='" + username2 + "') and user2_id in (select id from accounts where username='" + username1 + "'))";
                MySqlCommand sc = new MySqlCommand(sql, conn);
                sc.ExecuteNonQuery();
                conn.Close();
                return "You and " + username2 + " now aren't friends!";
            }
            else return "You and " + username2 + " aren't friends!";
        }

        [WebMethod]
        public String getMyRequest(String username)
        {
            if (isUsername(username).Equals("OK"))
            {
                MySqlConnection conn = new MySqlConnection(connection);
                conn.Open();
                String sql = "select username from accounts where id in (select receiver_id from accounts,friend_requests where accounts.id=friend_requests.sender_id and username='" + username + "')";
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();

                DataTable dt = ds.Tables[0];
                String requestlist = "";

                foreach (DataRow dr in dt.Rows)
                {
                    requestlist += dr["username"].ToString() + "-";
                }
                if (requestlist.Length > 0) { if (requestlist[requestlist.Length - 1].ToString().Equals("-")) requestlist = requestlist.Substring(0, requestlist.Length - 1); }
                else requestlist = "Nobody";
                return requestlist;
            }
            else return "This username is not exist!";
        }

        [WebMethod]
        public DataSet FindFriend(String username)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select accounts.id,username,name,phone,email,address,latitude,longitude,time from friends,accounts where user1_id = accounts.id and user2_id in (select id from accounts where username='" + username + "') union select accounts.id,username,name,phone,email,address,latitude,longitude,time from friends,accounts where user2_id = accounts.id and user1_id in (select id from accounts where username='" + username + "')";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();
            return ds;
        }

        [WebMethod]
        public DataSet getFriendFollowUsername(String username)
        {
            MySqlConnection conn = new MySqlConnection(connection);
            conn.Open();
            String sql = "select * from accounts where username='"+username+"'";
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            conn.Close();
            return ds;
        }
    }
}